﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class MatHangDAO
    {
        public List<MatHangDTO> HienThi()
        {
            //string sql = "select mh.MAMH, mh.TENHANG, dv.TENDVT, mh.SOLUONGTON from MATHANG mh, DONVITINH dv where mh.MADVT=dv.MADVT";
            string sql = "select * from MATHANG";
            SqlConnection conn = DataProvider.ConnectionDB();
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataReader reader = command.ExecuteReader();

            List<MatHangDTO> danhsach = new List<MatHangDTO>();
            while (reader.Read())
            {
                MatHangDTO dto = new MatHangDTO();
                dto.MAMH = reader.GetString(0);
                dto.TENHANG = reader.GetString(1);
                dto.MADVT = reader.GetString(2);
                dto.SOLUONGTON = reader.GetInt32(3);
                danhsach.Add(dto);
            }
            conn.Close();
            return danhsach;
        }

        public int ThemMH(MatHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "insert into MATHANG values(";
            sql += "N'" + dto.MAMH + "',";
            sql += "N'" + dto.TENHANG + "',";
            sql += "N'" + dto.MADVT + "',";
            sql += "N'" + dto.SOLUONGTON + "')";

            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int KiemTraMAMH(MatHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "Select count(*) from MATHANG where MAMH=";
            sql += "'" + dto.MAMH + "'";
            SqlCommand command = new SqlCommand(sql, conn);
            int kq = (int)command.ExecuteScalar();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int CapNhat(MatHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "update MATHANG ";
            sql += "set MAMH=";
            sql += "N'" + dto.MAMH + "',";
            sql += "TENHANG=";
            sql += "N'" + dto.TENHANG + "',";
            sql += "MADVT=";
            sql += "N'" + dto.MADVT + "',";
            sql += "SOLUONGTON=";
            sql += "'" + dto.SOLUONGTON + "' ";
            sql += "where MAMH=";
            sql += "'" + dto.MAMH + "'";

            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int Xoa(MatHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "Delete MATHANG ";
            sql += "where MAMH=";
            sql += "'" + dto.MAMH + "'";
            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }
    }
}
